package com.mililoan.persist.system.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.mililoan.common.bean.PageResultSet;
import com.mililoan.common.dao.AbstractBaseDao;
import com.mililoan.common.dao.JdbcPageResultSetDao;
import com.mililoan.common.utils.DateUtil;
import com.mililoan.common.utils.StackTraceUtil;
import com.mililoan.common.utils.Tool;
import com.mililoan.model.system.User;
import com.mililoan.persist.system.UserDao;

/**
 * 用户表操作实体类
 * 
 * @author mwei
 *
 */
@Repository("userDao")
public class UserDaoImpl extends AbstractBaseDao<User> implements UserDao {
	private static final Logger LOGGER = LoggerFactory.getLogger(UserDaoImpl.class);
	private static final String SELECT_SQL = " SELECT T.*,D.DEP_NAME FROM SYS_USER T LEFT JOIN SYS_DEPARTMENT D ON T.department_code = D.DEP_CODE ";

	/**
	 * 查询用户
	 * 
	 * @param user,page,pageSize
	 * @return 用户ID
	 * @author mwei
	 */
	@SuppressWarnings("unchecked")
	public PageResultSet<User> query(User user, int page, int pageSize) {
		// 查询需要的参数
		ArrayList<Object> rag = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				SELECT_SQL + " WHERE T.ISVALID='1'");
		if (StringUtils.isNotEmpty(user.getUserAcct())) {
			sql.append(" AND T.USER_ACCT LIKE ? ");
			rag.add("%" + user.getUserAcct() + "%");
		}
		if (StringUtils.isNotEmpty(user.getUserName())) {
			sql.append(" AND T.USER_NAME LIKE ? ");
			rag.add("%" + user.getUserName() + "%");
		}
		if (user.getDepartmentCode()>0) {
			sql.append(" AND T.DEPARTMENT_CODE=? ");
			rag.add(user.getDepartmentCode());
		}
		if (null != user.getActorCodes() && user.getActorCodes().size() > 0 && user.getActorCodes().get(0) > 0) {
			sql.append(" AND T.ID IN (SELECT USER_ID FROM SYS_USER_FUNC WHERE PTYPE='A' AND CODE=?) ");
			rag.add(user.getActorCodes().get(0));
		}
		if (!StringUtils.isEmpty(user.getActorCode())) {
			sql.append(" AND T.ID IN (SELECT USER_ID FROM SYS_USER_FUNC WHERE PTYPE='A' AND CODE=?) ");
			rag.add(user.getActorCode());
		}
		sql.append("ORDER BY T.ID");

		JdbcTemplate template = this.getJdbcTemplate();
		final JdbcPageResultSetDao<User> pageDao = new JdbcPageResultSetDao<User>(pageSize);
		pageDao.setPage(page);

		final List<User> pageList = pageDao.getPrs().getResult();
		PageResultSet<User> l_pageResultSet = null;

		try {
			l_pageResultSet = pageDao.pageResultSet(sql.toString(), template, rag.toArray(), new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					User rsUser = buildUser(rs);
					pageList.add(rsUser);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("--------分页查询用户时出错------------");
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			return null;
		}
		return l_pageResultSet;
	}

	/**
	 * 查询所有用户
	 * 
	 * @param user,page,pageSize
	 * @return 用户ID
	 * @author mwei
	 */
	public ArrayList<User> queryAll(User user) {
		// 查询需要的参数
		final ArrayList<User> infoList = new ArrayList<User>();
		ArrayList<Object> rag = new ArrayList<Object>();
//		StringBuffer sql = new StringBuffer(
//				"SELECT T.*,D.DEP_NAME FROM SYS_USER T LEFT JOIN T_DEPARTMENT D ON T.DEPARTMENT_CODE=D.DEPAREMENT_CODE WHERE T.FLAG='1'");
		StringBuffer sql = new StringBuffer(SELECT_SQL + " WHERE T.ISVALID='1'");
		if (user.getDepartmentCode()>0) {
			sql.append(" AND T.DEPARTMENT_CODE=? ");
			rag.add(user.getDepartmentCode());
		}
		if (null != user.getActorCodes() && user.getActorCodes().size() > 0 && user.getActorCodes().get(0) > 0) {
			sql.append(" AND T.ID IN (SELECT USER_ID FROM SYS_USER_FUNC WHERE PTYPE='A' AND CODE=?) ");
			rag.add(user.getActorCodes().get(0));
		}
		sql.append("ORDER BY T.ID");

		JdbcTemplate template = this.getJdbcTemplate();
		try {
			template.query(sql.toString(), rag.toArray(), new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					User rsUser = buildUser(rs);
					infoList.add(rsUser);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("--------查询全部用户时出错------------");
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			return null;
		}
		return infoList;
	}

	public ArrayList<User> queryAllExc(User user) {
		// 查询需要的参数
		final ArrayList<User> infoList = new ArrayList<User>();
		ArrayList<Object> rag = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				SELECT_SQL + " WHERE T.ISVALID='1'");
		if (null != user.getNoUserCodes()) {
			sql.append(" AND T.ID NOT IN (" + Tool.insertString(user.getNoUserCodes(), ",") + ") ");
		}
		sql.append("ORDER BY T.ID");

		JdbcTemplate template = this.getJdbcTemplate();
		try {
			template.query(sql.toString(), rag.toArray(), new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					User rsUser = buildUser(rs);
					infoList.add(rsUser);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			return null;
		}
		return infoList;
	}

	/**
	 * 添加用户
	 * 
	 * @param user
	 * @return 用户ID
	 * @author mwei
	 */
	public Long addUser(final User user) {
		JdbcTemplate template = this.getJdbcTemplate();
		Long newId = new Long(0);
		final String sql = "INSERT INTO SYS_USER(USER_ACCT,USER_PWD,USER_NAME,SEX,DEPARTMENT_CODE,TELEPHONE,MOBILE,EMAIL,USER_LOGINTIME,USER_LOGINIP,ISVALID" 					
				+ ") VALUES (?,?,?,?,?,?,?,?,?,?,?)";
		
		try {
			//返回主键
			KeyHolder keyHolder = new GeneratedKeyHolder();
			template.update(new  PreparedStatementCreator(){
				@SuppressWarnings("unused")
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {					
					PreparedStatement ps = con.prepareStatement(sql, 
								PreparedStatement.RETURN_GENERATED_KEYS);
							ps.setString(1, user.getUserAcct());
							ps.setString(2, user.getUserPwd());
							ps.setString(3, user.getUserName());
							ps.setString(4, user.getSex());
							ps.setLong(5, user.getDepartmentCode());
							ps.setString(6, user.getTelephone());
							ps.setString(7, user.getMobile());
							ps.setString(8, user.getEmail());
							ps.setString(9, user.getUserLogintime());
							ps.setString(10, user.getUserLoginip());
							ps.setString(11, user.getIsvalid());
					return ps;
				}
			},keyHolder);
			//获取主键
			newId = keyHolder.getKey().longValue();
		} catch (Exception e) {			
			e.printStackTrace();
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			return null;
		}	
		return newId;
	}

	/**
	 * 修改用户
	 * 
	 * @param user
	 * @return 用户ID
	 * @author mwei
	 */
	public Long updateUser(final User user) {
		JdbcTemplate template = this.getJdbcTemplate();
		String sql = "UPDATE SYS_USER T SET T.USER_ACCT=?,T.USER_PWD=?,T.USER_NAME=?,T.SEX=?,T.DEPARTMENT_CODE=?,T.TELEPHONE=?,T.MOBILE=?,T.EMAIL=?,T.USER_LOGINTIME=?,T.USER_LOGINIP=?,T.ISVALID=?"
				+ " WHERE T.ID=?";
		//取得新的code
		try {
			template.update(sql, new PreparedStatementSetter() {			
				public void setValues(PreparedStatement ps) throws SQLException {
						ps.setString(1, user.getUserAcct());
						ps.setString(2, user.getUserPwd());
						ps.setString(3, user.getUserName());
						ps.setString(4, user.getSex());
						ps.setLong(5, user.getDepartmentCode());
						ps.setString(6, user.getTelephone());
						ps.setString(7, user.getMobile());
						ps.setString(8, user.getEmail());
						ps.setString(9, user.getUserLogintime());
						ps.setString(10, user.getUserLoginip());
						ps.setString(11, user.getIsvalid());
						ps.setLong(12, user.getId());
				}
			});
		} catch (DataAccessException e) {
			e.printStackTrace();
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			return null;
		}
		return user.getId();
	}

	/**
	 * 修改密码
	 * 
	 * @param userId,password
	 * @return 用户ID
	 * @author mwei
	 */
	public Long updatePassword(Long userId, String password) {
		JdbcTemplate template = this.getJdbcTemplate();
		try {
			String sql = "UPDATE SYS_USER T SET T.USER_PWD=? WHERE T.ID=?";
			template.update(sql, new Object[] { password, userId });
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			return null;
		}
		return userId;
	}

	/**
	 * 删除用户
	 * 
	 * @param userId
	 * @return 用户ID
	 * @author mwei
	 */
	public Long delUser(Long userId) {
		JdbcTemplate template = this.getJdbcTemplate();
		try {
			String sql = "UPDATE SYS_USER T SET T.ISVALID='0' WHERE T.ID=?";
			template.update(sql, new Object[] { userId });
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			return null;
		}
		return userId;
	}

	/**
	 * 根据用户名和密码获得用户信息
	 * 
	 * @param accounts
	 * @param password
	 * @return user 如果没有记录返回NULL
	 * @author mwei
	 */
	public User getUser(String accounts, String password) {
		String sql = SELECT_SQL + " WHERE T.USER_ACCT=? AND T.USER_PWD=? AND T.ISVALID='1'";
		Object[] args = { accounts, password };
		User user = this.getUser(sql, args);
		return user;
	}

	/**
	 * 根据用户名ID得用户信息
	 * 
	 * @param id
	 * @return user 如果没有记录返回NULL
	 * @author mwei
	 */
	public User getUser(Long id) {
		String sql = SELECT_SQL + " WHERE T.ID=? AND T.ISVALID='1'";
		Object[] args = { id };
		User user = this.getUser(sql, args);
		return user;
	}

	/**
	 * 根据用户名帐号得用户信息
	 * 
	 * @param accounts
	 * @return user 如果没有记录返回NULL
	 * @author mwei
	 */
	public User getUser(String accounts) {
		String sql = SELECT_SQL + " WHERE T.USER_ACCT=? AND T.ISVALID='1'";
		LOGGER.error("accounts=" + accounts);
		Object[] args = { accounts };
		User user = this.getUser(sql, args);
		return user;
	}

	/**
	 * 查询USER方法(私有)
	 * 
	 * @param sql
	 * @param args
	 * @return user 如果没有记录返回NULL
	 * @author mwei
	 */
	private User getUser(String sql, Object[] args) {
		JdbcTemplate template = this.getJdbcTemplate();
		final User user = new User();
		try {
			template.query(sql, args, new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					user.setId(rs.getLong("ID"));
					user.setUserAcct(rs.getString("USER_ACCT"));
					user.setUserPwd(rs.getString("USER_PWD"));
					user.setUserName(rs.getString("USER_NAME"));
					user.setSex(rs.getString("SEX"));
					user.setDepartmentCode(rs.getLong("DEPARTMENT_CODE"));
					user.setTelephone(rs.getString("TELEPHONE"));
					user.setMobile(rs.getString("MOBILE"));
					user.setEmail(rs.getString("EMAIL"));
					user.setUserLogintime(rs.getString("USER_LOGINTIME"));
					user.setUserLoginip(rs.getString("USER_LOGINIP"));
					user.setIsvalid(rs.getString("ISVALID"));
					user.setDepartmentName(rs.getString("DEP_NAME"));
				}
			});

		} catch (DataAccessException e) {
			e.printStackTrace();
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			return null;
		}
		return (user.getId()>0) ?  user: null;
	}
	
	private User buildUser(ResultSet rs) {
	 	User rsUser = new User();
	 	try {
			rsUser.setId(rs.getLong("ID"));
			rsUser.setUserAcct(rs.getString("USER_ACCT"));
			rsUser.setUserPwd(rs.getString("USER_PWD"));
			rsUser.setUserName(rs.getString("USER_NAME"));
			rsUser.setSex(rs.getString("SEX"));
			rsUser.setDepartmentCode(rs.getLong("DEPARTMENT_CODE"));
			rsUser.setTelephone(rs.getString("TELEPHONE"));
			rsUser.setMobile(rs.getString("MOBILE"));
			rsUser.setEmail(rs.getString("EMAIL"));
			rsUser.setUserLogintime(rs.getString("USER_LOGINTIME"));
			rsUser.setUserLoginip(rs.getString("USER_LOGINIP"));
			rsUser.setIsvalid(rs.getString("ISVALID"));
			rsUser.setDepartmentName(rs.getString("DEP_NAME"));
		} catch (Exception e) {
            LOGGER.error(StackTraceUtil.getStackTrace(e));
        }
		return rsUser;
	 }
}
